#!/usr/bin/env python
# -*- coding: utf-8 -*-

from utils import log

from datetime import datetime
from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy import Table, MetaData, Column, ForeignKey, Integer, Float, Boolean, String, Unicode, UnicodeText, \
    DateTime, UniqueConstraint, func
from controls.data_pool_mysql import engine

metadata = MetaData()
Session = sessionmaker()
Session.configure(bind=engine)

"""card_collect collection has
    userid:          用户id (int)
    ssid:            用户设备的ssid (str)
    cityid:          城市id (int)
    cardid:          卡牌id (int)
    ctime:           收藏时间(datetime)
"""


class CardCollectItem(object):
    def to_dict(self):
        res = {}
        res['ssid'] = self.ssid
        res['userid'] = self.userid
        res['cityid'] = self.cityid
        res['cardid'] = self.cardid
        res['ctime'] = self.ctime
        return res

    def __repr__(self):
        return "userid:%d, ssid:%s, cityid:%d, cardid:%d" % (self.userid, self.ssid, self.cityid, self.cardid)


card_collect_table = Table(
    'card_collect', metadata,
    Column('id', Integer, primary_key=True),
    Column('ssid', Unicode(65), nullable=False),
    Column('userid', Integer, default=0),
    Column('cityid', Integer, nullable=False),
    Column('cardid', Integer, nullable=False),
    Column('ctime', DateTime, default=datetime.now, nullable=False),
)

mapper(CardCollectItem, card_collect_table)


def create_card_collect_table_if_not_exist():
    metadata.create_all(engine)


# collect a card
def add_card_collect_item(p_item):
    create_card_collect_table_if_not_exist()
    item = CardCollectItem()
    item.ssid = p_item['ssid']
    item.userid = p_item['userid']
    item.cityid = p_item['cityid']
    item.cardid = p_item['cardid']
    try:
        session = Session()
        res = session.query(func.count(CardCollectItem.ssid)).filter(
            CardCollectItem.cityid == p_item['cityid'], CardCollectItem.cardid == p_item['cardid'],
            CardCollectItem.ssid == p_item['ssid'])
        if res.scalar() > 0:
            return True
        session.add(item)
        session.flush()
        session.commit()
        return True
    except Exception, e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()


# delete a card collect
def delete_card_collect_item(p_item):
    if not p_item:
        return False
    try:
        session = Session()
        session.query(CardCollectItem).filter(
            CardCollectItem.ssid == p_item['ssid'],
            CardCollectItem.cityid == p_item['cityid'],
            CardCollectItem.cardid == p_item['cardid']).delete()
        session.flush()
        session.commit()
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()


def is_collected(p_cityid, p_cardid, p_ssid):
    try:
        session = Session()
        res = session.query(func.count(CardCollectItem.ssid)).filter_by(
            cityid=p_cityid, cardid=p_cardid, ssid=p_ssid)
        return res.scalar() > 0
    except Exception as e:
        log.exp(e)
        return None
    finally:
        session.close()


def get_collect_count_by_cityid_cardid(p_cityid, p_cardid):
    try:
        session = Session()
        res = session.query(func.count(CardCollectItem.ssid)).filter(CardCollectItem.cityid == p_cityid,
                                                                     CardCollectItem.cardid == p_cardid)
        return res.scalar()
    except Exception as e:
        log.exp(e)
        return None
    finally:
        session.close()


# get_collect_count_by_cityid
def get_collect_count_by_cityid(p_cityid):
    try:
        session = Session()
        item_list = session.query(CardCollectItem.cardid, func.count(CardCollectItem.ssid)).filter(
            CardCollectItem.cityid == p_cityid).group_by(CardCollectItem.cardid).having(
            func.count(CardCollectItem.ssid) > 0)

        if not item_list:
            return None
        '''
        >>> from sqlalchemy import func
        SQL>>> session.query(func.count(User.name), User.name).group_by(User.name).all()  
        [(1, u'ed'), (1, u'fred'), (1, u'mary'), (1, u'wendy')]
        '''

        res = {}
        for item in item_list:
            res[item[0]] = item[1]
        return res
    except Exception as e:
        log.exp(e)
        return None
    finally:
        session.close()


if __name__ == "__main__":
    create_card_collect_table_if_not_exist()
